﻿namespace Hidistro.SqlDal.Commodities
{
    using Hidistro.Core;
    using Hidistro.Core.Enums;
    using Hidistro.Entities.Commodities;
    using Hidistro.Membership.Context;
    using Hidistro.SqlDal.Members;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Runtime.InteropServices;
    using System.Text;
    using System.Text.RegularExpressions;

    public class ProductApiDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        public DataSet GetProductsByQuery(ProductQuery query, int gradeId, out int totalrecord)
        {
            DataSet set = new DataSet();
            StringBuilder builder = new StringBuilder();
            builder.Append(" 1=1 ");
            if (query.SaleStatus != ProductSaleStatus.All)
            {
                builder.AppendFormat(" AND SaleStatus = {0}", (int) query.SaleStatus);
            }
            else
            {
                builder.AppendFormat(" AND SaleStatus not in ({0})", 0);
            }
            if (query.BrandId.HasValue)
            {
                builder.AppendFormat(" AND BrandId = {0}", query.BrandId.Value);
            }
            if (query.TagId.HasValue)
            {
                builder.AppendFormat("AND ProductId IN (SELECT ProductId FROM Hishop_ProductTag WHERE TagId={0})", query.TagId);
            }
            if (!string.IsNullOrEmpty(query.Keywords))
            {
                query.Keywords = DataHelper.CleanSearchString(query.Keywords);
                string[] strArray = Regex.Split(query.Keywords.Trim(), @"\s+");
                builder.AppendFormat(" AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[0]));
                for (int i = 1; (i < strArray.Length) && (i <= 4); i++)
                {
                    builder.AppendFormat("AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[i]));
                }
            }
            if (!string.IsNullOrEmpty(query.ProductCode))
            {
                builder.AppendFormat(" AND ProductCode LIKE '%{0}%'", DataHelper.CleanSearchString(query.ProductCode));
            }
            if (query.CategoryId.HasValue && (query.CategoryId.Value > 0))
            {
                builder.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%'  OR ExtendCategoryPath LIKE '{0}|%' )", query.MaiCategoryPath);
            }
            if (query.IsMakeTaobao.HasValue && (query.IsMakeTaobao.Value >= 0))
            {
                builder.AppendFormat(" AND IsMaketaobao={0}", query.IsMakeTaobao.Value);
            }
            if (query.PublishStatus != PublishStatus.NotSet)
            {
                if (query.PublishStatus == PublishStatus.Notyet)
                {
                    builder.Append(" AND TaobaoProductId = 0");
                }
                else
                {
                    builder.Append(" AND TaobaoProductId <> 0");
                }
            }
            if (query.StartDate.HasValue)
            {
                builder.AppendFormat(" AND AddedDate >='{0}'", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
            }
            if (query.EndDate.HasValue)
            {
                builder.AppendFormat(" AND AddedDate <='{0}'", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
            }
            string str = "ProductId,ProductName,ProductCode,ThumbnailUrl60,isnull(MarketPrice,0) as MarketPrice,SaleStatus,isnull(SalePrice,0) as SalePrice,Weight,ShowSaleCounts as SaleCounts, ShortDescription,Stock,AddedDate,[Description]";
            if (gradeId > 0)
            {
                int discount = new MemberGradeDao().GetMemberGrade(gradeId).Discount;
                str = str + string.Format(",CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) = 1 ", gradeId) + string.Format("THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END AS RankPrice", gradeId, discount);
            }
            else
            {
                str = str + ",SalePrice as RankPrice";
            }
            string str2 = "";
            string str3 = "";
            if (query.SortOrder == SortAction.Desc)
            {
                str3 = "desc";
            }
            if (!string.IsNullOrEmpty(query.SortBy))
            {
                str2 = " order by " + query.SortBy + " " + str3;
            }
            else
            {
                str2 = " order by ProductId " + str3;
            }
            string str4 = string.Concat(new object[] { "SELECT TOP ", query.PageSize, " ", str, " from vw_Hishop_BrowseProductList as p WHERE ", builder.ToString(), str2, ";" });
            if (query.PageIndex > 1)
            {
                str4 = string.Concat(new object[] { "SELECT TOP ", query.PageSize, " ", str, " from vw_Hishop_BrowseProductList as p WHERE ProductId not in (SELECT ProductId from (SELECT TOP ", (query.PageIndex - 1) * query.PageSize, " ProductId FROM vw_Hishop_BrowseProductList WHERE ", builder.ToString(), str2, ") as T) AND ", builder.ToString(), str2, ";" });
            }
            str4 = (str4 + "select ProductId,SkuId,SKU,Stock,isnull(SalePrice,0) as SalePrice  from dbo.Hishop_SKUs;") + "SELECT COUNT(*) AS SumRecord FROM vw_Hishop_BrowseProductList WHERE " + builder.ToString();
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(str4);
            using (set = this.database.ExecuteDataSet(sqlStringCommand))
            {
                set.Relations.Add("ProductRealation", set.Tables[0].Columns["ProductId"], set.Tables[1].Columns["ProductId"], false);
            }
            totalrecord = Convert.ToInt32(set.Tables[2].Rows[0]["SumRecord"].ToString());
            return set;
        }

        public DataSet GetProductSkuDetials(int productId, int gradeId)
        {
            DataSet set = new DataSet();
            string query = "";
            int discount = 100;
            if (string.IsNullOrEmpty(productId.ToString()) || (Convert.ToInt32(productId) <= 0))
            {
                return set;
            }
            query = "SELECT ProductId,ProductName,ProductCode,ImageUrl1,ImageUrl2,ImageUrl3,ImageUrl4,ImageUrl5,ThumbnailUrl60,isnull(MarketPrice,0)  as MarketPrice,SaleStatus,isnull(SalePrice,0) as SalePrice, Weight,SaleCounts, ShortDescription,Stock,AddedDate,[Description] from vw_Hishop_BrowseProductList WHERE ProductId=" + productId + ";";
            if (gradeId > 0)
            {
                Member user = HiContext.Current.User as Member;
                discount = new MemberGradeDao().GetMemberGrade(gradeId).Discount;
                object obj2 = query + "SELECT ProductId,SkuId, SKU,Stock,";
                query = (string.Concat(new object[] { obj2, " (CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = s.SkuId AND GradeId = ", gradeId, ") = 1" }) + string.Format(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = s.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END) as SalePrice", gradeId, discount)) + ",Weight,isnull(CostPrice,0) as CostPrice" + string.Format(" FROM Hishop_SKUs s WHERE ProductId =" + productId, new object[0]);
            }
            else
            {
                query = query + "SELECT ProductId,SkuId, SKU,Stock,isnull(SalePrice,0) as SalePrice,Weight, CostPrice FROM Hishop_SKUs WHERE ProductId =" + productId;
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
            return this.database.ExecuteDataSet(sqlStringCommand);
        }

        public DataTable GetSkuStocks(string productIds)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("SELECT p.ProductId,ProductName, SkuId, SKU, Stock,ThumbnailUrl40 FROM Hishop_Products p JOIN Hishop_SKUs s ON p.ProductId = s.ProductId WHERE p.ProductId IN ({0})", DataHelper.CleanSearchString(productIds));
            builder.Append(" SELECT SkuId, AttributeName, ValueStr FROM Hishop_SKUItems si JOIN Hishop_Attributes a ON si.AttributeId = a.AttributeId JOIN Hishop_AttributeValues av ON si.ValueId = av.ValueId");
            builder.AppendFormat(" WHERE si.SkuId IN(SELECT SkuId FROM Hishop_SKUs WHERE ProductId IN ({0}))", DataHelper.CleanSearchString(productIds));
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            DataTable table = null;
            DataTable table2 = null;
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                table = DataHelper.ConverDataReaderToDataTable(reader);
                reader.NextResult();
                table2 = DataHelper.ConverDataReaderToDataTable(reader);
            }
            table.Columns.Add("SKUContent");
            if ((((table != null) && (table.Rows.Count > 0)) && (table2 != null)) && (table2.Rows.Count > 0))
            {
                foreach (DataRow row in table.Rows)
                {
                    string str = string.Empty;
                    foreach (DataRow row2 in table2.Rows)
                    {
                        if (((string) row["SkuId"]) == ((string) row2["SkuId"]))
                        {
                            object obj2 = str;
                            str = string.Concat(new object[] { obj2, row2["AttributeName"], "：", row2["ValueStr"], "; " });
                        }
                    }
                    row["SKUContent"] = str;
                }
            }
            return table;
        }

        public bool UpdateSkuStock(Dictionary<string, int> skuStocks)
        {
            StringBuilder builder = new StringBuilder();
            foreach (string str in skuStocks.Keys)
            {
                builder.AppendFormat(" UPDATE Hishop_SKUs SET Stock = {0} WHERE SkuId = '{1}'", skuStocks[str], DataHelper.CleanSearchString(str));
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public bool UpdateSkuStock(string productIds, int stock)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("UPDATE Hishop_SKUs SET Stock = {0} WHERE ProductId IN ({1})", stock, DataHelper.CleanSearchString(productIds)));
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }
    }
}

